from Accounting.Entity.Tree import EntityTree
from DataAccess.DBConnFactory import DBConnFactory
from string import Template
import timeit


def settle_fees(ref_date):
	#DEBUG, Performance tuning
	#start = timeit.default_timer()

	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = conn.cursor()
	
	sql_tpl = Template('''select mv.portfolio_id,mv.mkt_val,mv.currency
					from portfolio_mkt_val mv
					where mv.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat())) 	
	r = cursor.fetchall()
	for item in r:
		master_id = item[0]
		portf_mkt_val = item[1]
		curncy = item[2]
		
		master = EntityTree().find_node(master_id)
		if master:
			master.settlement(ref_date, portf_mkt_val, curncy)			
		else:
			raise RuntimeError(), ('Master entity not exist, id='+master_id)
	
	#elapsed = (timeit.default_timer() - start)
	#print 'daily_clearing complete, ', elapsed
	
	
def reflect_to_portf_cash_account(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	acct_conn = DBConnFactory().get_db_connection('ACCOUNTING')
	acct_cursor = acct_conn.cursor()
	
	#update to portfolio cash account
	sql_tpl = Template('''select * from (
						select cc.entity,sum(cc.amount) amount,cc.curncy
						from cash_chg cc
						join portfolio_info@PKE_DB pi
						  on pi.portfolio_id=cc.entity
						  and cc.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and cc.detail<>'m2m_adj'
						  and cc.detail<>'others' 
						  and cc.detail<>'av_adj' 
						group by cc.entity,cc.curncy) 
						where amount<>0''')
	acct_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	#print 'debug, cash_chg=', acct_cursor.fetchall() , ref_date.isoformat() 
	r = [(ref_date.isoformat(),)+item for item in acct_cursor.fetchall()]
	#print 'debug, cash_chg=', r
	pke_cursor.executemany('''insert into cash_switch
							values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)''', r)
	pke_conn.commit()
		
	# sql_tpl = Template('''update 
					# (select /*+BYPASS_UJVC*/ cs.amount as cf,pos.amount
					# from cash_switch cs
					# join position pos 
						# on pos.ref_date=cs.ref_date 
						# and pos.portfolio_id=cs.portfolio_id 
						# and pos.price_currency=cs.currency
						# and pos.security_type='CASH' 
						# and pos.ticker='9999999'
					# where cs.ref_date=TO_DATE('${DATE}','yyyy-mm-dd'))
					# set amount=amount+cf
					# ''')   
					
	sql_tpl = Template('''merge into position pos
					using (select ref_date,portfolio_id,currency,amount 
							from cash_switch
							where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')) t
					on (pos.ref_date=t.ref_date 
						and pos.portfolio_id=t.portfolio_id
						and pos.price_currency=t.currency
						and pos.security_type='CASH'
						and pos.ticker='9999999')
					when matched then update set 
						pos.amount=pos.amount+t.amount
					when not matched then insert 
						(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
						values(t.ref_date,t.portfolio_id,'9999999','CASH',t.amount,1.0,t.currency)
					''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
